﻿using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using WPFExcelReport.Helpers;
namespace WPFExcelReport
{
    public partial class BaseFunction
    {
        public SqlConnection SqlServerDB;
        public bool IsOnline = true;
        bool IsHasLocal = true;
        public DataSet TryConnect(string CmdType)
        {
            DataSet set=null;
         
            if (IsOnline)
            {
                switch(CmdType)
                {
                    default:
                        TryConnect();
                        break;
                    case "SignalRSTART":
                    case "CHKZLAPP":
                    case "ZLANSTART":
                    case "ZLANSET":
                    case "ZLANGET":
                    case "ZLANRESET":
                    case "ZLANSTOP":
                    case "ZLANSTOPAM":
                    case "ZLANCHK": 
                        dicQueryParamater.Add(CmdType,DateTime.Now.ToString());
                        set=proxy.RequestAgentStart(dicQueryParamater);
                        break; 
                } 
            } 
            return set;
        }
        public DataSet TryConnect()
        {
            DataSet set = null;

            if (IsOnline)
            {
                set = proxy.GetDataSetByQuery(dicQueryParamater);

            }
            if (set.Tables.Count == 0)
            {
                if (set.DataSetName == "COMFAILED")
                {
                    IsOnline = false;
                }
            }
            if (!IsOnline && IsHasLocal)
            {
                set = GetDataSetByQuery(dicQueryParamater);
            }
            if (set.Tables.Count == 0)
            {
                if (set.DataSetName == "COMFAILED")
                {
                    IsHasLocal = false;
                    throw new Exception("AGENTRECHECK");
                }
            }
            else
            {
                if (set.Tables[0].TableName == "ERRORTB")
                {
                    string error = UserID + ":";
                    foreach (DataRow dwAgent in set.Tables[0].Rows)
                    {
                        error += ";Error:" + dwAgent["MESSAGE"].ToString();
                        error += ";With:" + dwAgent["SQLPM"].ToString();
                        error += "Execute:" + dwAgent["SQLTEXT"].ToString();
                    }
                    throw new Exception(error);
                }
            }
            return set;
        }
        public DataSet Connect2Proxy(OracleCommand oracmd, string CommandType)
        {            
            if (proxy.State == System.ServiceModel.CommunicationState.Closed)
            {
                Tracer.Info("BaseFunction", "通讯协议已经关闭");
                return dsResult;
            }
            dicQueryParamater.Clear(); 
            dicQueryParamater.Add("DBTYPE", CommandType);
            dicQueryParamater.Add("CommandText", oracmd.CommandText);
            dicQueryParamater.Add("CommandType", oracmd.CommandType.ToString());
            dicQueryParamater.Add("ConnectionString", oracmd.Connection.ConnectionString);
            foreach (OracleParameter cmp in oracmd.Parameters)
            {
                if(CommandType=="ORACLEBLOB")
                { 
                    dicQueryParamater.Add(cmp.ParameterName.ToString(),cmp.Value); 
                }
                else
                {
                    dicQueryParamater.Add(cmp.ParameterName.ToString(), cmp.Value.ToString());

                }
            } 

            return TryConnect(CommandType);
        }
        public DataSet Connect2Proxy(OracleCommand oracmd)
        { 
            if (proxy.State == System.ServiceModel.CommunicationState.Closed)
                throw new Exception("通讯协议已经关闭");
            dicQueryParamater.Clear();
            dicQueryParamater.Add("CommandText", oracmd.CommandText);
            dicQueryParamater.Add("CommandType", oracmd.CommandType.ToString());
            dicQueryParamater.Add("ConnectionString", oracmd.Connection.ConnectionString);
            foreach (OracleParameter cmp in oracmd.Parameters)
            {
                dicQueryParamater.Add(cmp.ParameterName.ToString(), cmp.Value.ToString());
            }
            
            return TryConnect();
        }
        public DataSet Connect2Proxy(SqlCommand sqlcommand)
        { 
            dicQueryParamater.Clear();
            dicQueryParamater.Add("DBTYPE", "SQLSERVER");
            dicQueryParamater.Add("CommandText", sqlcommand.CommandText);
            dicQueryParamater.Add("CommandType", sqlcommand.CommandType.ToString());
            dicQueryParamater.Add("ConnectionString", sqlcommand.Connection.ConnectionString);
            foreach (SqlParameter cmp in sqlcommand.Parameters)
            {
                dicQueryParamater.Add(cmp.ParameterName.ToString(), cmp.Value.ToString());
            } 
            return TryConnect();
        }
        public DataSet Connect2Proxy(ITXConnectInfo client)
        {
            dicQueryParamater.Clear(); 
            dicQueryParamater.Add("DBTYPE", "ITXCLIENT");
            dicQueryParamater.Add("ITXSRV", client.Server);
            dicQueryParamater.Add("ITXPORT", client.Port);
            dicQueryParamater.Add("COMMAND", client.Command);
            dicQueryParamater.Add("TRANS", client.Trans); 
            dicQueryParamater.Add("DATAMAP", client.DataMap);  
            return TryConnect();
        }

        public SqlCommand sql;
        public SqlDataAdapter mySqlDa;
        public OracleCommand Command;
        public string Connection = "";
        public OracleDataAdapter myDa;
        public string State = "Query";
        public bool IsOnTransation = false;
        public void StartTransation()
        {
            if (Command == null)
                return;
            if (Command.Connection.State == ConnectionState.Closed)
                Command.Connection.Open();
            Command.Transaction = Command.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
            IsOnTransation = true;

        }
        public void RollBack()
        {
            Command.Transaction.Rollback();
            IsOnTransation = false;
            Command.Connection.Close();
        }
        public void Commit()
        {
            Command.Transaction.Commit();
            IsOnTransation = false;
            Command.Connection.Close();
        }
        public void SetUpOracle(Dictionary<string, object> dicSrcParamater)
        {
            Connection = (string)dicSrcParamater["ConnectionString"];
            string CommandT = (string)dicSrcParamater["CommandType"];
            if (Command != null)
            {
                int pc = Command.Parameters.Count;
                for (int i = 0; i < pc; i++)
                {
                    Command.Parameters.RemoveAt(0);
                }
                if (Command.Connection.ConnectionString.Trim() == Connection.Trim())
                {
                    if (Command.Connection.State == ConnectionState.Closed)
                    {
                        Command.Connection.Open();
                    }

                }
                if (CommandT == "StartTransation")
                {
                    StartTransation();
                    State = "Transation";
                }
                if (CommandT == "Commit")
                {
                    Commit();
                    State = "Transation";
                }
                if (CommandT == "RollBack")
                {
                    RollBack();
                    State = "Transation";
                }
                if (CommandT == "Text")
                {
                    State = "Query";
                }

                return;
            }
            Command = new OracleCommand();
            Command.CommandType = CommandType.Text;

            foreach (KeyValuePair<string, object> kvp in dicSrcParamater)
            {
                switch (kvp.Key)
                {
                    case "DBTYPE":
                    case "DataTable":
                    case "AGENTID":
                    case "CommandType":
                        break;
                    case "CommandText":
                        Command.CommandText = (string)dicSrcParamater["CommandText"];
                        break;
                    case "ConnectionString":
                        Connection = (string)dicSrcParamater["ConnectionString"];
                        if (Command.Connection == null)
                        {
                            Command.Connection = new OracleConnection(Connection);
                        }
                        if (Command.Connection.State == ConnectionState.Closed)
                            Command.Connection.Open();
                        break;
                    default:
                        break;
                }
            }
        }
        public void SetUpSQLServer(Dictionary<string, object> dicSrcParamater)
        {

            string conn = (string)dicSrcParamater["ConnectionString"];
            if (sql != null)
            {
                int pcx = sql.Parameters.Count;
                for (int i = 0; i < pcx; i++)
                {
                    sql.Parameters.RemoveAt(0);
                }
                if (sql.Connection.ConnectionString.Trim() == conn.Trim())
                {
                    if (sql.Connection.State == ConnectionState.Closed)
                    {
                        sql.Connection.Open();
                    }
                    return;
                }
            }
            sql = new SqlCommand();
            sql.CommandType = CommandType.Text;
            foreach (KeyValuePair<string, object> kvp in dicSrcParamater)
            {
                switch (kvp.Key)
                {
                    case "DBTYPE":
                        break;
                    case "AGENTID":
                        break;
                    case "CommandText":
                        break;
                    case "ConnectionString":
                        conn = (string)dicSrcParamater["ConnectionString"];
                        sql.Connection = new SqlConnection(conn);
                        if (sql.Connection.State == ConnectionState.Closed)
                            sql.Connection.Open();
                        break;
                    case "CommandType":
                        break;
                    default:
                        break;
                }
            }
        }
        public DataSet GetDataSetByQuery(Dictionary<string, object> dicSrcParamater)
        {
            DataSet dsResult = new DataSet();

            try
            {
                string commandtype = "CommandText";
                string strCommandType;
                string dbType = "Oracle";
                if (dicSrcParamater.ContainsKey("DBTYPE"))
                {
                    dbType = dicSrcParamater["DBTYPE"].ToString();
                }
                SqlParameter sqlParameter;
                string tb = "Table";
                switch (dbType)
                {

                    case "SQLSERVER":
                        #region=====SQLSERVER========
                        SetUpSQLServer(dicSrcParamater);
                        foreach (KeyValuePair<string, object> kvp in dicSrcParamater)
                        {
                            switch (kvp.Key)
                            {
                                case "DBTYPE":
                                case "AGENTID":
                                case "ConnectionString":
                                    break;
                                case "DataTable":
                                    tb = (string)dicSrcParamater["DataTable"];
                                    break;
                                case "CommandType":
                                    strCommandType = (string)dicSrcParamater["CommandType"];
                                    switch (strCommandType)
                                    {
                                        case "StoredProcedure":
                                            sql.CommandType = CommandType.StoredProcedure;
                                            commandtype = "StoredProcedure";
                                            break;
                                        case "TableDirect":
                                            sql.CommandType = CommandType.TableDirect;
                                            commandtype = "TableDirect";
                                            break;
                                        default:
                                            sql.CommandType = CommandType.Text;
                                            break;
                                    }
                                    break;
                                case "CommandText":
                                    sql.CommandText = (string)dicSrcParamater["CommandText"];
                                    break;
                                default:
                                    sqlParameter = new SqlParameter();
                                    sqlParameter.ParameterName = kvp.Key;
                                    sqlParameter.Value = (string)kvp.Value;
                                    sql.Parameters.Add(sqlParameter);
                                    break;
                            }
                        }
                        if (mySqlDa == null)
                        {
                            mySqlDa = new SqlDataAdapter(sql);
                        }
                        mySqlDa.Fill(dsResult);
                        dsResult.Tables[0].TableName = tb;
                        break;

                        #endregion
                    case "ORACLE":
                    default:
                        #region===ORACLE==========
                        SetUpOracle(dicSrcParamater);
                        if (State == "Transation")
                        {
                            return dsResult;
                        }
                        OracleParameter catCMDP;
                        foreach (KeyValuePair<string, object> kvp in dicSrcParamater)
                        {
                            switch (kvp.Key)
                            {
                                case "DBTYPE":
                                case "AGENTID":
                                case "ConnectionString":
                                    break;
                                case "DataTable":
                                    tb = (string)dicSrcParamater["DataTable"];
                                    break;
                                case "CommandType":
                                    strCommandType = (string)dicSrcParamater["CommandType"];
                                    switch (strCommandType)
                                    {
                                        case "StoredProcedure":
                                            Command.CommandType = CommandType.StoredProcedure;
                                            commandtype = "StoredProcedure";
                                            break;
                                        case "TableDirect":
                                            Command.CommandType = CommandType.TableDirect;
                                            commandtype = "TableDirect";
                                            break;
                                        default:
                                            Command.CommandType = CommandType.Text;
                                            break;
                                    }
                                    break;
                                case "CommandText":
                                    Command.CommandText = (string)dicSrcParamater["CommandText"];
                                    break;
                                default:
                                    catCMDP = new OracleParameter();
                                    catCMDP.ParameterName = kvp.Key;
                                    catCMDP.Value = (string)kvp.Value;
                                    Command.Parameters.Add(catCMDP);
                                    break;
                            }
                        }
                        if (commandtype == "StoredProcedure")
                        {
                            //Tracer.Verbose("Service", "StoredProcedure");
                            //MessageBox.Show("Dear Sir," + "StoredProcedure");
                            Command.ExecuteNonQuery();
                            Command.Connection.Close();
                            return dsResult;
                        }
                        if (Command.CommandText.StartsWith("UPDATE")
                            || Command.CommandText.StartsWith("INSERT")
                            || Command.CommandText.StartsWith("DELETE")
                             )
                        {
                            Command.ExecuteNonQuery(); ;
                            return dsResult;
                        }

                        if (myDa == null)
                        {
                            myDa = new OracleDataAdapter(Command);
                            myDa.ContinueUpdateOnError = true;
                        }
                        myDa.Fill(dsResult);
                        dsResult.Tables[0].TableName = tb;
                        break;
                        #endregion
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsResult;
        }
    }
}
